Inline View Subquery

At times it is useful to create a subquery that is used within the FROM clause as if it were a table name. This is known as an inline view or inline view subquery. The SQL statement in the inline view defines the source of the data for the FROM clause.

Creating the Main Query

The process is essentially the same as used for the Correlated subquery discussed in a previous example.

Create the main query to return all Purchase Orders with invoice_reqd field = “Y”.

The SELECT clause:

And the WHERE clause:

Creating the Subquery

Click the Add Query button to add the inline view subquery.

The build subquery editor displays, and allows you to select a table to work with. Use the fields in the table and the tabs at the bottom of the page to build your subquery. For example, you might select a field from a table, and add a condition (the field equals a specific value, for example).

If you want to limit the query to vendors located in California, you would create the inline view based on the vendor_state field of the Vendors table:

The SELECT clause:

And the WHERE clause:

Use the Rename button at the bottom of the editor page to give your subquery a user-friendly name, such as "CA_Vendors," so that you can recognize it later.

Select OK to return to editing the main query.

Placing the Subquery in the Main Query

The new subquery displays in the main query editor in a window similar to a table.

Make sure that the Visible Fields (SELECT) tab is active at the bottom of the editor, then double-click a field from your new subquery to add it to the main query as the target for the FROM clause. In this example, add the vendor_state field.

Note that if you are using an Oracle database, you must enclose the field name in quotes. The dropdown for the Field cell provides both options (with and without quotes).

In most cases, you would then join the vendor_ID field from the Purchase_Orders table to the new CA_Vendors table:

This results in the following query:

SELECT Purchase_Orders.vendor_ID,
Purchase_Orders.PO_ID,
Purchase_Orders.invoice_recd,
CA_Vendors.VendorState
FROM Purchase_Orders left join ( select Vendors.vendor_state as VendorState,
Vendors.vendor_ID
FROM Vendors
WHERE Vendors.vendor_state ='ca' ) CA_Vendors on Purchase_Orders.vendor_ID = CA_Vendors.vendor_ID
WHERE Purchase_Orders.invoice_recd ='y'

Results

Executing the query yields the results shown below, listing the vendors in California whose invoices have been received.